package com.alibaba.alicare.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelText {

	private String filePath = "D:\\WorkSpaces\\AlibabaWorkspace\\mail\\file\\xiecc.xls";

	public static void main(String[] args) {
		ExcelText e = new ExcelText();
		System.out.println(e.filePath);
		e.parser(e.getExcelFile(e.filePath));
	}

	public void parser(Object obj) {
		if (obj instanceof XSSFWorkbook) {
			XSSFWorkbook xwb = (XSSFWorkbook) obj;
			this.parserExcelx(xwb);
		} else if (obj instanceof HSSFWorkbook) {
			HSSFWorkbook hwb = (HSSFWorkbook) obj;
			this.parserExcelH(hwb);
		}
	}

	public Object getExcelFile(String filePath) {
		File file = new File(filePath);
		InputStream in;
		String fileName = file.getName();
		String suffix = this.getFileSuffix(fileName);
		Object excel = null;
		if (suffix != null && "xls".equalsIgnoreCase(suffix)) {
			try {
				in = new FileInputStream(file);
				excel = new XSSFWorkbook(in);
				System.out.println("======");
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else if (suffix != null && "xlsx".equalsIgnoreCase(suffix)) {
			try {
				in = new FileInputStream(file);
				excel = new HSSFWorkbook(in);
				System.out.println("======");
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return excel;
	}

	public String getFileSuffix(String fileName) {
		return fileName.substring(fileName.indexOf(".") + 1);
	}

	public void parserExcelx(XSSFWorkbook xwb) {
		for (int k = 0; k < xwb.getNumberOfSheets(); k++) {
			XSSFSheet sheet = xwb.getSheetAt(k);
			int rows = sheet.getPhysicalNumberOfRows();
			System.out.println("Sheet " + k + " \"" + xwb.getSheetName(k)
					+ "\" has " + rows + " row(s).");
			for (int r = 0; r < rows; r++) {
				XSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}

				int cells = row.getPhysicalNumberOfCells();
				System.out.println("\nROW " + row.getRowNum() + " has " + cells
						+ " cell(s).");
				for (int c = 0; c < cells; c++) {
					XSSFCell cell = row.getCell(c);
					String value = null;

					switch (cell.getCellType()) {

					case HSSFCell.CELL_TYPE_FORMULA:
						value = "FORMULA value=" + cell.getCellFormula();
						break;

					case HSSFCell.CELL_TYPE_NUMERIC:
						value = "NUMERIC value=" + cell.getNumericCellValue();
						break;

					case HSSFCell.CELL_TYPE_STRING:
						value = "STRING value=" + cell.getStringCellValue();
						break;

					default:
					}
					System.out.println("CELL col=" + cell.getColumnIndex()
							+ " VALUE=" + value);
				}
			}
		}
	}

	public void parserExcelH(HSSFWorkbook hwb) {
		for (int k = 0; k < hwb.getNumberOfSheets(); k++) {
			HSSFSheet sheet = hwb.getSheetAt(k);
			int rows = sheet.getPhysicalNumberOfRows();
			System.out.println("Sheet " + k + " \"" + hwb.getSheetName(k)
					+ "\" has " + rows + " row(s).");
			for (int r = 0; r < rows; r++) {
				HSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}

				int cells = row.getPhysicalNumberOfCells();
				System.out.println("\nROW " + row.getRowNum() + " has " + cells
						+ " cell(s).");
				for (int c = 0; c < cells; c++) {
					HSSFCell cell = row.getCell(c);
					String value = null;

					switch (cell.getCellType()) {

					case HSSFCell.CELL_TYPE_FORMULA:
						value = "FORMULA value=" + cell.getCellFormula();
						break;

					case HSSFCell.CELL_TYPE_NUMERIC:
						value = "NUMERIC value=" + cell.getNumericCellValue();
						break;

					case HSSFCell.CELL_TYPE_STRING:
						value = "STRING value=" + cell.getStringCellValue();
						break;

					default:
					}
					System.out.println("CELL col=" + cell.getColumnIndex()
							+ " VALUE=" + value);
				}
			}
		}
	}
}
